package POI;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import standart.Student;

public class PoiParseJson {

	public void CreateXls(String outputFile, Student[] students)
			throws IOException {
		FileOutputStream out = new FileOutputStream(outputFile);
		HSSFWorkbook workBook = new HSSFWorkbook();
		workBook.createSheet();
		workBook.setSheetName(0, "Students");
		HSSFSheet sheet = workBook.getSheetAt(0);

		// Cell color for title variables
		HSSFCellStyle goldCellStyle = workBook.createCellStyle();
		goldCellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
		goldCellStyle.setFillBackgroundColor(HSSFColor.GOLD.index);
		goldCellStyle.setFillPattern(HSSFColor.WHITE.index);

		Field[] fields = Student.class.getDeclaredFields();

		List<String> fieldList = new ArrayList<String>();

		for (int i = 0; i < fields.length; i++) {
			if (!fields[i].getName().equals("tutor")) {
				fieldList.add(fields[i].getName());
			}
		}

		for (Student student : students) {
			// Creating titleRow
			Row titleRow = sheet.createRow(sheet.getLastRowNum() + 1);

			for (int i = 0; i < fieldList.size(); i++) {
				Cell cell = titleRow.createCell(i);
				cell.setCellValue(fieldList.get(i));
				cell.setCellStyle(goldCellStyle);
			}
			Row row = sheet.createRow(sheet.getLastRowNum() + 1);
			// Creating cell for each row
			for (int cellNumb = 0; cellNumb < titleRow.getLastCellNum(); cellNumb++) {
				Cell cell = row.createCell(cellNumb);
				cell.setCellValue(student.getFields().get(
						titleRow.getCell(cellNumb).getStringCellValue()));

			}
			// Creating tutor row
			Row tutorRow = sheet.createRow(sheet.getLastRowNum() + 1);

			Cell tutorCell = tutorRow.createCell(0);
			tutorCell.setCellValue("tutor");
			tutorCell.setCellStyle(goldCellStyle);

			Cell tutorSomeCell = tutorRow.createCell(1);
			tutorSomeCell.setCellValue("some");
			tutorSomeCell.setCellStyle(goldCellStyle);

			Row tutorSomeRow = sheet.createRow(sheet.getLastRowNum() + 1);
			Cell cell2 = tutorSomeRow.createCell(2);
			cell2.setCellValue(student.getTutor().getSome());

			// Making some space between students

			sheet.createRow(sheet.getLastRowNum() + 1);
		}

		workBook.write(out);
		out.close();

	}

	// this method only for test
	public void CreateXls(String outputFile) throws IOException {
		FileOutputStream out = new FileOutputStream(outputFile);
		HSSFWorkbook workBook = new HSSFWorkbook();
		workBook.createSheet();
		workBook.setSheetName(0, "Test");
		HSSFSheet sheet = workBook.getSheetAt(0);

		Row titleRow = sheet.createRow(0);
		for (int i = 0; i < 10; i++) {
			Cell cell = titleRow.createCell(i);
			cell.setCellValue("test " + i);
			System.out.println(cell.getStringCellValue());
		}
		workBook.write(out);
		out.close();

	}

	// this method only for test
	public void ReadXls(String outputFile) throws IOException {
		FileInputStream in = new FileInputStream(outputFile);
		POIFSFileSystem fileSystem = new POIFSFileSystem(in);
		HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
		HSSFSheet sheet = workBook.getSheetAt(0);
		Iterator<Row> it = sheet.rowIterator();

		if (it.hasNext()) {
			it.next();
		}

		while (it.hasNext()) {
			HSSFRow row = (HSSFRow) it.next();
			for (int i = 0; i < row.getLastCellNum(); i++) {
				System.out.println(row.getCell(i));
			}
		}
	}

}
